<?php
/**
 * Description of Emms_Report_DisbursementsByFund
 *
 * @author andres
 */
class Emms_Report_DisbursementsByFund extends HTech_Report
{
    protected function _setName() 
    {
        $this->_name = $this->translate('Loan disbursements consolidated by fund');
    }

    protected function _setForm() 
    {
        $this->_form = new HTech_Report_Form_DateRange();
    }
    
    protected function _setIndex() {}

    protected function _setSql() 
    {
        $this->_sql = '
            select 
                FundName,
                count(LoanId) Disbursements,
                sum(if(LoanCycle=1,1,0)) FirstTime,
                sum(if(ClientGender="Female",1,0)) Female,
                sum(if(ClientGender="Male",1,0)) Male,
                sum(if(BorrowerType="B",1,0)) Bank,
                sum(if(BorrowerType="I",1,0)) Individual,
                sum(if(CommunityType="Rural",1,0)) Rural,
                sum(if(CommunityType="Urban",1,0)) Urban,
                sum(if(CommunityType="Suburban",1,0)) Suburban,
                sum(Principal) Principal,
                sum(LoanInsuranceMnt) LoanInsurance,
                sum(HealthInsuranceMnt) HealthInsurance,
                sum(AdministrativeFeeMnt) AdministrativeFee,
                sum(BalancePrincipal) BalancePrincipal,
                sum(BalanceLoanInsurance) BalanceLoanInsurance,
                sum(BalanceHealthInsurance) BalanceHealthInsurance,
                sum(BalanceAdministrativeFee) BalanceAdministrativeFee,
                sum(if(Status="Disbursed",1,0)) Disbursed,
                sum(if(Status="Paid",1,0)) Paid,
                sum(if(Status="Written-Off",1,0)) WrittenOff,
                sum(if(Status="Recovered",1,0)) Recovered
            from 
                view_loans
            where 
                DeliveryDate >= :dateFrom and 
                DeliveryDate <= :dateTo
            group by
                FundId
            order by
                FundId';
    }
    
    protected function _setHeaders()
    {
        $this->_headers = new stdClass();
        $this->_headers->FundName = $this->translate('Fund');
        $this->_headers->Disbursements = $this->translate('Disbursements');
        $this->_headers->FirstTime = $this->translate('First cycle');
        $this->_headers->Female = $this->translate('Female borrowers');
        $this->_headers->Male = $this->translate('Male borrowers');
        $this->_headers->Bank = $this->translate('Bank borrowers');
        $this->_headers->Individual = $this->translate('Individual borrowers');
        $this->_headers->Rural = $this->translate('Rural borrowers');
        $this->_headers->Urban = $this->translate('Urban borrowers');
        $this->_headers->Suburban = $this->translate('Sub-urban borrowers');
        $this->_headers->Disbursed = $this->translate('Currently active');
        $this->_headers->Paid = $this->translate('Currently paid');
        $this->_headers->WrittenOff = $this->translate('Currently written-off');
        $this->_headers->Recovered = $this->translate('Currently recovered');
        $this->_headers->Principal = $this->translate('Principal disbursed');
        $this->_headers->LoanInsurance = $this->translate('Loan insurance');
        $this->_headers->HealthInsurance = $this->translate('Health insurance');
        $this->_headers->AdministrativeFee = $this->translate('Admin. fee');
        $this->_headers->BalancePrincipal = $this->translate('Principal current balance');
        $this->_headers->BalanceLoanInsurance = $this->translate('Loan insurance current balance');
        $this->_headers->BalanceHealthInsurance = $this->translate('Health insurance current balance');
        $this->_headers->BalanceAdministrativeFee = $this->translate('Admin. fee current balance');
    }
    
    public function setParams($params) 
    {
        $this->_params = array(
            ':dateFrom' => $params['DateFrom'],
            ':dateTo' => $params['DateTo']);
    }

}
